package com.zhucai.credit.repository;

import java.math.BigDecimal;
import java.util.List;

import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.zhucai.credit.model.CreditApplicationEntity;

/**
 * 
 * @author hsg
 *（申请贷款机构资质de）Repository
 */
@Repository
public interface CreditApplicationRepository extends
		JpaRepository<CreditApplicationEntity,Long>{

	@Query(value="select txpsup.enterprise_name sup_enterprise_name,fca.applicant_name,fca.applicant_mobile,fca.cr_apply_time,"
			+ " fbt.cr_prod_bank_name,fca.id ids,fca.cr_aproval_status,fca.cr_prodstatus,fca.cr_prod_start_time,"
			+ " fca.cr_applicant_id,fca.cr_applicant_pur_id,fca.id,fecps.enterprise_name pur_enterprise_name,fca.term_of_validity,"
			+ " fca.cr_prod_end_time,fca.apply_amt,fca.contract_amt,fbt.cr_prod_type,fca.reject_remark,fca.this_years_growth_rate"
			+ " from finance_credit_application fca "
			+ " left join finance_ent_account fecp on fecp.id=fca.cr_applicant_id or fca.applicant_enterprise_id = fecp.enterprise_id"
			+ " left join tb_xa_supplier txpsup on txpsup.id=fca.applicant_enterprise_id"
			+ " left join finance_ent_account fecps on fecps.id=fca.cr_applicant_pur_id"
			+ " left join finance_product_type fbt on fca.cr_prod_id=fbt.id"
			+ " where fca.status<>?1"
			+ " and (?2 is null or txpsup.enterprise_name like concat('%',?2,'%'))"
			+ " and (?3 is null or fecps.enterprise_name like concat('%',?3,'%'))"
			+ " and fca.cr_aproval_status in (?4)"
			+ " and (?5 is null or fca.cr_applicant_pur_id=?5)"
			+ " and (?6 is null or fecp.id=?6)"
			+ " and (?7 is null or fca.cr_prod_id=?7)"
			+ " order by ?#{#pageable}"
			,nativeQuery=true)
	List<Object[]> findBusList(int delete, String enterpriseSupName,String enterprisePurName, List<String> coopStatusList, Long crApplicantPurId, Long crApplicantId,Long crProdId, Pageable pageable);

	@Query(value="select count(1)"
			+ " from finance_credit_application fca "
			+ " left join finance_ent_account fecp on fecp.id=fca.cr_applicant_id or fca.applicant_enterprise_id = fecp.enterprise_id"
			+ " left join tb_xa_supplier txpsup on txpsup.id=fca.applicant_enterprise_id"
			+ " left join finance_ent_account fecps on fecps.id=fca.cr_applicant_pur_id"
			+ " left join finance_product_type fbt on fca.cr_prod_id=fbt.id"
			+ " where fca.status<>?1"
			+ " and (?2 is null or txpsup.enterprise_name like concat('%',?2,'%'))"
			+ " and (?3 is null or fecps.enterprise_name like concat('%',?3,'%'))"
			+ " and fca.cr_aproval_status in (?4)"
			+ " and (?5 is null or fca.cr_applicant_pur_id=?5)"
			+ " and (?6 is null or fecp.id=?6)"
			+ " and (?7 is null or fca.cr_prod_id=?7)"
			,nativeQuery=true)
	Integer findBusCount(int delete, String enterpriseSupName, String enterprisePurName, List<String> coopStatusList, Long crApplicantPurId, Long crApplicantId, Long crProdId);
	
	/**
	 * 查询各个状态的合作商家数量
	 * @param delete
	 * @param object
	 * @param object2
	 * @param crApplicantPurId
	 * @param crApplicantId
	 * @param object3
	 * @return
	 */
	@Query(value="select count(1)"
			+ " from finance_credit_application fca "
			+ " left join finance_product_type fbt on fca.cr_prod_id=fbt.id"
			+ " where fca.status<>?1"
			+ " and fca.cr_aproval_status in (?2)"
			+ " and (?3 is null or fca.cr_applicant_pur_id=?3)"
			+ " and (?4 is null or fca.cr_applicant_id=?4)"
			+ " and (?5 is null or fca.cr_prod_id=?5)"
			,nativeQuery=true)
	Integer findCooBankCount(int delete, List<String> bankStatusList, Long crApplicantPurId, Long crApplicantId, Long crProdId);

	/**
	 * 根据采购商和供应商金融id查询合作筑保通信息
	 * @author shoffice
	 * @version 2018年8月22日
	 * @param delete
	 * @param tid
	 * @param tid2
	 * @return
	 */
	@Query(value="select fca "
			+ " from CreditApplicationEntity fca "
			+ " where fca.status<>?1"
			+ " and fca.crAprovalStatus='BANK_SUCCESS'"
			+ " and fca.crApplicantPurId=?2"
			+ " and fca.crApplicantId=?3"
			)
	List<CreditApplicationEntity> findCreditAppBySupIdAndPurIdAndOver(int delete, Long purId, Long supId);

	/**
	 * 查询与核心企业合作的所有供应商企业id
	 * @version 2018年8月22日
	 * @param delete
	 * @param purid
	 * @return
	 */
	@Query(value="select fea.enterprise_id from "
			+ " finance_credit_application fca "
			+ " left join finance_ent_account fea on fca.cr_applicant_id=fea.id"
			+ " where fca.status<>?1"
			+ " and fca.cr_applicant_pur_id=?2"
			+ " and fca.cr_aproval_status='BANK_SUCCESS'"
			,nativeQuery=true)
	List<Long> findSupList(int delete, Long purid);
	/**
	 * 查询与供应商合作的所有采购商企业id
	 * @version 2018年8月22日
	 * @param delete
	 * @param purid
	 * @return
	 */
	@Query(value="select fea.enterprise_id from "
			+ " finance_credit_application fca "
			+ " left join finance_ent_account fea on fca.cr_applicant_pur_id=fea.id"
			+ " where fca.status<>?1"
			+ " and fca.cr_applicant_id=?2"
			+ " and fca.cr_aproval_status='BANK_SUCCESS'"
			,nativeQuery=true)
	List<Object[]> findPurList(int delete, Long purid);
	
	/**
	 * 根据报名编号查询资质申请记录
	 * @date 2017年12月13日 @user hsg
	 */
	CreditApplicationEntity findByAppNumAndStatusNot(String regist_num, int delete);


	/**
	 * 根据采购商组织号和供应商组织号查询有效的申请记录
	 * @date 2017年12月14日 @user hsg
	 */
	@Query(value="select creditApplicationEntity "
			+ " from CreditApplicationEntity creditApplicationEntity"
			+ " where creditApplicationEntity.status<>3"
			+ " and creditApplicationEntity.appOrgnNum=?1"
			+ " and creditApplicationEntity.cooOrgnNum=?2"
			+ " and creditApplicationEntity.crAprovalStatus=?3"
			+ " and creditApplicationEntity.crProdId = ?4")
	CreditApplicationEntity findPassCreditApplication(String cust_orgn_code, String r_cust_orgn_code, String string,Long crProdId);


	/**
	 * 查询是否有待银行审核的信息
	 * @param string
	 * @param delete
	 * @return
	 */
	List<CreditApplicationEntity> findByCrAprovalStatusAndStatusNot(String string, int delete);



	/**
	 * 查询合作中的信息
	 * @author hsg
	 * @version 2018年7月13日
	 * @param delete
	 * @param tid
	 * @param tid2
	 * @param string
	 * @return
	 */
	@Query(value="select * "
			+ " from finance_credit_application fca "
			+ " where 1=1"
			+ " and fca.status<>?1"
			+ " and fca.cr_applicant_id=?2"
			+ " and fca.cr_applicant_pur_id=?3"
			+ " and fca.cr_aproval_status = ?4"
			+ " and fca.cr_prod_id = ?5"
			,nativeQuery=true)
	CreditApplicationEntity findCoopInfo(int delete, Long supId, Long purId, String crProdstatus,Long crProdId);

	
	/**
	 * 更新状态
	 * @version 2018年8月1日
	 * @param tid
	 * @param crAprovalStatus
	 * @param crProdstatus
	 * @return
	 */
	@Transactional
	@Modifying(clearAutomatically=true)
	@Query(value="update finance_credit_application set cr_aproval_status = ?2,cr_prodstatus = ?3 where id =?1",nativeQuery=true)
	Integer updateStatus(Long tid, String crAprovalStatus, String crProdstatus);

	/**
	 * 
	 * @version 2018年8月1日
	 * @param string
	 * @param tid
	 * @return
	 */
	@Transactional
	@Modifying(clearAutomatically=true)
	@Query(value="update finance_credit_application set cr_aproval_status = ?1 where id =?2",nativeQuery=true)
	Integer updateCrAprovalStatus(String crAprovalStatus, Long tid);
	
	/**
	 * 根据供应商企业id和采购商金融id和银行类型id查询在审核中的资质申请记录
	 * @version 2018年8月13日
	 * @param tid
	 * @param supEntId
	 * @param tid2
	 * @return
	 */
	@Query(value="select * from finance_credit_application where cr_prod_id = ?1 and applicant_enterprise_id = ?2 and cr_applicant_pur_id = ?3 and status <> 3 and cr_aproval_status not in ('OVER')",nativeQuery=true)
	CreditApplicationEntity findBySupEntId(Long bankId, Long supEntId, Long purAccId);


	/**
	 * 工商银行推荐供应商时更新联系人信息
	 * @version 2018年8月13日
	 * @param tid
	 * @param applicantName
	 * @param applicantPhone
	 * @return
	 */
	@Transactional
	@Modifying(clearAutomatically=true)
	@Query(value="update finance_credit_application set applicant_name = ?2 , applicant_phone = ?3 where id =?1",nativeQuery=true)
	Integer updateLinkInfo(Long tid, String applicantName, String applicantPhone);

	@Query(value="select txsup.enterprise_name sup_enterprise_name,txsup.linkeman_name,"
			+ " txsup.linkman_phone,txpur.enterprise_name pur_enterprise_name,"
			+ " fbt.cr_prod_bank_name,fbt.cr_prod_type,fca.applicant_name,fca.applicant_mobile"
			+ " from finance_credit_application fca "
			+ " left join finance_ent_account fecp on fecp.id=fca.cr_applicant_id"
			+ " left join tb_xa_supplier txsup on txsup.id=fecp.enterprise_id or fca.applicant_enterprise_id = txsup.id"
			+ " left join finance_ent_account fecps on fecps.id=fca.cr_applicant_pur_id"
			+ " left join tb_xa_purchaser txpur on txpur.id=fecps.enterprise_id"
			+ " left join finance_product_type fbt on fca.cr_prod_id=fbt.id"
			+ " where fca.status<>3"
			+ " and fca.id = ?1"
			,nativeQuery=true)
	List<Object[]> findEntInfoById(Long tid);

	/**
	 * 工商银行模式下更新历史交易记录信息
	 * @version 2018年8月15日
	 * @param history_transaction_years
	 * @param integer
	 * @param historyDebtRate
	 * @param returnRate
	 * @param threeYearsSupply
	 * @param threeYearsReceivable
	 * @param twoYearsSupply
	 * @param twoYearsReceivable
	 * @param oneYearsSupply
	 * @param oneYearsReceivable
	 * @param thisYearsGrowthRate
	 * @param string2 
	 * @param mobil 
	 * @param long1 
	 * @return
	 */
	@Transactional
	@Modifying(clearAutomatically=true)
	@Query(value="update finance_credit_application set history_transaction_years = ?1 , history_dispute_count = ?2 , history_debt_rate = ?3"
			+ " , return_rate = ?4 , three_years_supply =?5 , three_years_receivable = ?6 , two_years_supply = ?7 "
			+ " , two_years_receivable =?8 , one_years_supply = ?9 , one_years_receivable = ?10"
			+ " , this_years_growth_rate = ?11 , cr_aproval_status = ?12 , reject_remark = ?13 , cr_applicant_id = ?14,applicant_mobile = ?16,applicant_name = ?17 where id =?15 and cr_aproval_status in('ICBC_HISTROY_INFO','ICBC_REJECT_PUR')",nativeQuery=true)
	Integer updateHistroyInfo(Double history_transaction_years, String history_dispute_count, BigDecimal historyDebtRate, BigDecimal returnRate, BigDecimal threeYearsSupply,
			BigDecimal threeYearsReceivable, BigDecimal twoYearsSupply, BigDecimal twoYearsReceivable, BigDecimal oneYearsSupply, BigDecimal oneYearsReceivable,
			BigDecimal thisYearsGrowthRate,String cr_aproval_status,String reject_remark,Long entId, Long creId, String mobil, String appName);

	/**
	 * 工商银行模式下 采购商审核通过供应商
	 * @version 2018年8月15日
	 * @param tid
	 * @return
	 */
	@Transactional
	@Modifying(clearAutomatically=true)
	@Query(value="update finance_credit_application set cr_aproval_status = 'ICBC_APPROVE_TWO' , reject_remark = null where id =?1 and cr_aproval_status = 'ICBC_WAIT_CONFIRM_PUR' and status <> 3",nativeQuery=true)
	Integer updatePurPass(Long tid);

	/**
	 * 工行模式下采购商驳回供应商
	 * @version 2018年8月15日
	 * @param tid
	 * @param rejectRemark 
	 * @return
	 */
	@Transactional
	@Modifying(clearAutomatically=true)
	@Query(value="update finance_credit_application set cr_aproval_status = 'ICBC_REJECT_PUR' , reject_remark = ?2 where id =?1 and cr_aproval_status = 'ICBC_WAIT_CONFIRM_PUR' and status <> 3",nativeQuery=true)
	Integer updatePurReject(Long tid, String rejectRemark);

	/**
	 * 工商模式下审核终止供应商
	 * @version 2018年8月15日
	 * @param tid
	 * @param rejectRemark
	 * @return
	 */
	@Transactional
	@Modifying(clearAutomatically=true)
	@Query(value="update finance_credit_application set cr_aproval_status = 'ICBC_STOP_PUR' , reject_remark = ?2 where id =?1 and cr_aproval_status = 'ICBC_WAIT_CONFIRM_PUR' and status <> 3",nativeQuery=true)
	Integer updatePurStop(Long tid, String rejectRemark);
	/**
	 * 工行模式下在核心企业终止下，重新推荐供应商
	 * @version 2018年8月15日
	 * @param tid
	 * @param supEntName 
	 * @param rejectRemark
	 * @return
	 */
	@Transactional
	@Modifying(clearAutomatically=true)
	@Query(value="update finance_credit_application set cr_aproval_status = 'ICBC_APPROVE_FIRST' ,reject_remark = null, applicant_name = ?2 , applicant_phone = ?3,applicant_enterprise_name=?4  where id =?1 and cr_aproval_status in ('ICBC_STOP_PUR','ICBC_FIRST_REJECT') and status <> 3",nativeQuery=true)
	Integer updateLinkInfoApprove(Long tid, String applicantName, String applicantPhone, String supEntName);

	
	/**
	 * 更新最新的操作记录id
	 * @author shoffice
	 * @version 2018年8月15日
	 * @param creTid
	 * @param operTid
	 * @return
	 */
	@Transactional
	@Modifying(clearAutomatically=true)
	@Query(value="update finance_credit_application set oper_record_id = ?2  where id =?1 and status <> 3",nativeQuery=true)
	Integer updateOperId(Long creTid, Long operTid);

	/**
	 * 查询核心企业和供应商对应的融资机构是否在合作中
	 * @version 2018年8月17日
	 * @param tid
	 * @param supEntId
	 * @param enterpriseId
	 * @param tid2
	 * @param tid3
	 * @return
	 */
	@Query(value="select count(*) from finance_credit_application "
			+ " where status <> 3 "
			+ " and cr_prod_id = ?1"
			+ " and cr_applicant_id = ?2"
			+ " and cr_applicant_pur_id = ?3"
			+ " and cr_aproval_status = 'BANK_SUCCESS'"
			+ " and cr_prodstatus = 'IN_COOPERATION'",nativeQuery=true)
	Integer findCoopStatus(Long bankId,Long supAccId, Long purAccId);

	/**
	 * 查询工行正在合作的企业信息
	 * @version 2018年8月20日
	 * @return
	 */
	@Query(value="select * from finance_credit_application f"
			+ " left join finance_product_type  p on  f.cr_prod_id = p.id"
			+ " left join finance_cr_record fcr on fcr.originator = f.cr_applicant_pur_id and "
			+ " fcr.participator = f.cr_applicant_id and fcr.cr_prod_id = f.cr_prod_id "
			+ " where f.status <> 3 "
			+ " and p.cr_prod_type = ?1"
			+ " and f.cr_aproval_status = 'BANK_SUCCESS'"
			+ " and f.cr_prodstatus = 'IN_COOPERATION'"
			+ " and fcr.qryf_seqno is not null and fcr.credit_status in('PENDING_WITHDRAWAL','REPAYMENT')"
			+ " group by f.cr_applicant_pur_id",nativeQuery=true)
	List<CreditApplicationEntity> findByIcbcEnterList(String prodTye);

	
	/**
	 * 更新供应商收款账号
	 * @version 2018年8月20日
	 * @param memberRecAcc
	 * @param tid
	 * @return
	 */
	@Transactional
	@Modifying(clearAutomatically=true)
	@Query(value="update finance_credit_application set sup_receivables_account = ?1  where id =?2",nativeQuery=true)
	Integer updateSupReceivables(String memberRecAcc, Long tid);



	
	@Transactional
	@Modifying(clearAutomatically=true)
	@Query(value="update finance_credit_application set cr_applicant_id = ?1  where applicant_enterprise_id =?2 and status <> 3 and cr_prod_id =?3 and cr_applicant_id = 999999999",nativeQuery=true)
	Integer updateICBCSepInfo( Long supAccId,Long supEntId,Long bankId);

	/**
	 * 自动更新金融采购商企业名称
	 * @version 2018年9月10日
	 * @return
	 */
	@Transactional
	@Modifying(clearAutomatically=true)
	@Query(value="update finance_ent_account t INNER JOIN tb_xa_purchaser p on p.id = t.enterprise_id and t.ent_type = 'PURCHASER' set "
			   + " t.enterprise_name = p.enterprise_name where t.enterprise_name <> p.enterprise_name",nativeQuery=true)
	Integer updatePurEntName();

	/**
	 * 自动更新金融供应商企业名称
	 * @version 2018年9月10日
	 * @return
	 */
	@Transactional
	@Modifying(clearAutomatically=true)
	@Query(value="update finance_ent_account t  INNER JOIN tb_xa_supplier s on s.id = t.enterprise_id and t.ent_type = 'SUPPLIER' "
			   + " set t.enterprise_name = s.enterprise_name where t.enterprise_name <> s.enterprise_name;",nativeQuery=true)
	Integer updateSupEntName();

	/**
	 * 根据供应商金融id和采购商金融id查询非过期失效的资质申请记录
	 * @author shoffice
	 * @version 2018年9月25日
	 * @param purAccId
	 * @param tid
	 * @return
	 */
	@Query(value="select * from finance_credit_application f "
			+ " left join finance_product_type fpt on fpt.id = f.cr_prod_id"
			+ " where f.cr_applicant_pur_id = ?1 and f.cr_applicant_id = ?2  "
			+ " and (f.cr_aproval_status <> 'OVER' or f.cr_prodstatus <> 'EXPIRED') and fpt.cr_prod_type = ?3"
			,nativeQuery = true)
	List<CreditApplicationEntity> findByPurAccIdAndSupAccId(Long purAccId, Long tid,String bankType);

	/**
	 * 根据供应商金融id和采购商金融id查询非过期失效的资质申请记录
	 * @author shoffice
	 * @version 2018年9月25日
	 * @param purAccId
	 * @param tid
	 * @return
	 */
	@Query(value="select * from finance_credit_application f "
			+ " left join finance_product_type fpt on fpt.id = f.cr_prod_id"
			+ " where f.cr_applicant_pur_id = ?1 and f.cr_applicant_id = ?2  "
			+ " and (f.cr_aproval_status <> 'OVER' or f.cr_prodstatus <> 'EXPIRED') and p.cr_prod_type = ?3"
			,nativeQuery = true)
	List<CreditApplicationEntity> findByPurAccIdAndSupAccIdOr(Long purAccId, Long tid, String bankType);

	/**
	 * 根据银行类型查询正常过期的资质申请记录
	 * @version 2018年10月11日
	 * @param string
	 * @return
	 */
	@Query(value="select fca.* from finance_credit_application fca "
			+ "LEFT JOIN finance_product_type fpt on fca.cr_prod_id = fpt.id "
			+ "where fpt.cr_prod_type = ?1 and fca.cr_prod_end_time <= SYSDATE() "
			+ "and fca.cr_aproval_status = 'BANK_SUCCESS' and fca.cr_prodstatus = 'IN_COOPERATION'",nativeQuery=true)
	List<CreditApplicationEntity> findBankOverdue(String cr_prod_type);



	








	





}
